﻿Imports System.IO
Imports System.Data.SqlClient



Public Class NewCasesAdminForm

    Public WhoCalled As String = ""
    Dim TheDisplaySQL As String = ""
    Dim TheRealSQL As String = ""
    Dim FireEvents As Boolean = False
    Dim TheScenario As String = ""
    Dim TheProduct As String = ""
    Dim ListBoxCalling As Boolean = False
    Dim ClearFields As Boolean = True
    Dim ScenarioBoxReady As Boolean = False

    Dim FormulaElements As New List(Of String)
    Dim SQLElemets As New List(Of String)

    Public TheCurrentRow As Integer
    Public ReloadRow As Integer

    Private Sub NewCasesAdminForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        LoadMe()

    End Sub

    Private Function CanAddElement(ByVal Element As String) As Boolean
        Dim AddE As Boolean = True

        If FormulaElements.Count = 1 Then
            If WhoCalled = "EditField" Then
                Return True
                Exit Function
            End If
        End If

        If FormulaElements.Count = 0 Then
            Return AddE
        Else
            Select Case Element
                Case "+", "-", "/", "*", ")"
                    Select Case Trim(FormulaElements(FormulaElements.Count - 1))
                        Case "+", "-", "/", "*", "("
                            AddE = False
                        Case Else
                            AddE = True
                    End Select
                Case Else
                    Select Case Trim(FormulaElements(FormulaElements.Count - 1))
                        Case "+", "-", "/", "*", "("
                            AddE = True

                        Case Else
                            AddE = False
                    End Select
            End Select
            
            Return AddE
        End If
    End Function

    Private Sub LoadMe()

        If WhoCalled = "" Then Exit Sub

        ClearForm()
        SaveNewButton.Visible = True
        PreviousButton.Visible = True
        NextButton.Visible = True

        If WhoCalled = "EditField" Then
            FillForm()
            SaveNewButton.Visible = False
        End If

        If WhoCalled = "AddField" Then
            PreviousButton.Visible = False
            NextButton.Visible = False
        End If

        FieldBox.Select(0, 0)
        FireEvents = True
    End Sub


    Sub LoadCombos()

        LoadComboBox(TableBox, "Select * From modelling_tablelist order by Table_description", "Table_Name", "Table_Description")
        TableBox.DropDownStyle = ComboBoxStyle.DropDownList
        LoadComboBox(TypeBox, "Select * From modelling_case_datatypes Order by Type_Description", "Type", "Type_Description")
        TypeBox.DropDownStyle = ComboBoxStyle.DropDownList

    End Sub

    Private Sub Save()


        Dim sql As String

        ClearFields = True

        If WhoCalled = "AddField" Then

            If UserButton.Checked = True Then

                CheckFields()
                If ClearFields = False Then Exit Sub

                Dim TheCode As Integer
                TheCode = GetFieldCode()

                sql = "Insert Into Modelling_Case_Templates Values ('" & NewCasesCreationForm.TheCaseID & "', " & NewCasesCreationForm.ThePeriod & _
                      ", " & TheCode & ", " & GetOrderCode() & ", '" & FieldBox.Text.ToString & "', '" & _
                      TypeBox.SelectedValue.ToString & "', 'User Input' , 'User Input', 'User Input', '" & TableBox.SelectedValue.ToString & "', 'No')"

                RunNonQuery(sql)
                With NewCasesCreationForm
                    .LoadFieldGrid(0)
                    .FieldGrid.Rows(.GetTheRow(TheCode, "Field_Code")).Selected = True
                End With
                Exit Sub

            End If

            If FormulaButton.Checked = True Then

                CheckFields()
                If ClearFields = False Then Exit Sub

                Dim TheCode As Integer
                TheCode = GetFieldCode()

                sql = "Insert Into Modelling_Case_Templates Values ('" & NewCasesCreationForm.TheCaseID & "', " & NewCasesCreationForm.ThePeriod & _
                      ", " & TheCode & ", " & GetOrderCode() & ", '" & FieldBox.Text.ToString & "', '" & _
                      TypeBox.SelectedValue.ToString & "', 'Formula' , '" & TheRealSQL & "', '" & TheDisplaySQL & "', '" & TableBox.SelectedValue.ToString & "', 'No')"

                RunNonQuery(sql)
                With NewCasesCreationForm
                    .LoadFieldGrid(0)
                    .FieldGrid.Rows(.GetTheRow(TheCode, "Field_Code")).Selected = True
                End With
                Exit Sub

            End If

            If ProductButton.Checked = True Then

                CheckFields()
                If ClearFields = False Then Exit Sub

                Dim TheCode As Integer
                TheCode = GetFieldCode()

                sql = "Insert Into Modelling_Case_Templates Values ('" & NewCasesCreationForm.TheCaseID & "', " & NewCasesCreationForm.ThePeriod & _
                      ", " & TheCode & ", " & GetOrderCode() & ", '" & FieldBox.Text.ToString & "', '" & _
                      TypeBox.SelectedValue.ToString & "', 'Product' , '" & Trim(ScenarioBox.SelectedValue.ToString) & "/" & Trim(ProductBox.SelectedValue.ToString) & _
                      "', '" & Trim(ScenarioBox.Text.ToString) & " - " & Trim(ProductBox.Text.ToString) & "', '" & TableBox.SelectedValue.ToString & "', 'No')"

                RunNonQuery(sql)
                With NewCasesCreationForm
                    .LoadFieldGrid(0)
                    .FieldGrid.Rows(.GetTheRow(TheCode, "Field_Code")).Selected = True
                End With
                Exit Sub

            End If

            If ApprovedTotalButton.Checked = True Then

                Dim TestData As DataView
                TestData = LoadDataView("Select * from Modelling_Case_Templates where DataCut_ID = '" & NewCasesCreationForm.TheCaseID & "' and Period_ID = " & _
                    NewCasesCreationForm.ThePeriod & " and Formula = 'Approved Total' ")

                If TestData.Count <> 0 Then
                    MessageBox.Show("You can only have one Approved Total field in each case. Please delete or edit the exisiting Approved Total field", "Approved Total Fields", MessageBoxButtons.OK)
                    Exit Sub
                End If


                CheckFields()
                If ClearFields = False Then Exit Sub

                Dim TheCode As Integer
                TheCode = GetFieldCode()

                sql = "Insert Into Modelling_Case_Templates Values ('" & NewCasesCreationForm.TheCaseID & "', " & NewCasesCreationForm.ThePeriod & _
                      ", " & TheCode & ", " & GetOrderCode() & ", '" & FieldBox.Text.ToString & "', '" & _
                      TypeBox.SelectedValue.ToString & "', 'Approved Total' , 'Approved Total', 'Approved Total', '" & TableBox.SelectedValue.ToString & "', 'No')"

                RunNonQuery(sql)
                With NewCasesCreationForm
                    .LoadFieldGrid(0)
                    .FieldGrid.Rows(.GetTheRow(TheCode, "Field_Code")).Selected = True
                End With
                Exit Sub

            End If

            Exit Sub

        End If

        If WhoCalled = "EditField" Then

            If UserButton.Checked = True Then

                CheckFields()
                If ClearFields = False Then Exit Sub

                sql = "Delete from Modelling_Case_Templates where DataCut_ID = '" & NewCasesCreationForm.TheCaseID & "' and Period_ID = " & _
                      NewCasesCreationForm.ThePeriod & " and Field_Code = " & NewCasesCreationForm.TheFieldCode

                RunNonQuery(sql)

                Dim TheCode As Integer
                TheCode = NewCasesCreationForm.TheFieldCode

                sql = "Insert Into Modelling_Case_Templates Values ('" & NewCasesCreationForm.TheCaseID & "', " & NewCasesCreationForm.ThePeriod & _
                      ", " & NewCasesCreationForm.TheFieldCode & ", " & NewCasesCreationForm.TheFieldOrder & ", '" & FieldBox.Text.ToString & "', '" & _
                      TypeBox.SelectedValue.ToString & "', 'User Input' , 'User Input', 'User Input', '" & TableBox.SelectedValue.ToString & "', 'No')"

                RunNonQuery(sql)
                With NewCasesCreationForm
                    .LoadFieldGrid(0)
                    .FieldGrid.Rows(.GetTheRow(TheCode, "Field_Code")).Selected = True
                End With
                Exit Sub

            End If

            If ApprovedTotalButton.Checked = True Then

                CheckFields()
                If ClearFields = False Then Exit Sub

                sql = "Delete from Modelling_Case_Templates where DataCut_ID = '" & NewCasesCreationForm.TheCaseID & "' and Period_ID = " & _
                      NewCasesCreationForm.ThePeriod & " and Field_Code = " & NewCasesCreationForm.TheFieldCode

                RunNonQuery(sql)

                Dim TheCode As Integer
                TheCode = NewCasesCreationForm.TheFieldCode

                sql = "Insert Into Modelling_Case_Templates Values ('" & NewCasesCreationForm.TheCaseID & "', " & NewCasesCreationForm.ThePeriod & _
                      ", " & NewCasesCreationForm.TheFieldCode & ", " & NewCasesCreationForm.TheFieldOrder & ", '" & FieldBox.Text.ToString & "', '" & _
                      TypeBox.SelectedValue.ToString & "', 'Approved Total' , 'Approved Total', 'Approved Total', '" & TableBox.SelectedValue.ToString & "', 'No')"

                RunNonQuery(sql)
                With NewCasesCreationForm
                    .LoadFieldGrid(0)
                    .FieldGrid.Rows(.GetTheRow(TheCode, "Field_Code")).Selected = True
                End With
                Exit Sub

            End If

            If FormulaButton.Checked = True Then

                CheckFields()
                If ClearFields = False Then Exit Sub

                sql = "Delete from Modelling_Case_Templates where DataCut_ID = '" & NewCasesCreationForm.TheCaseID & "' and Period_ID = " & _
                      NewCasesCreationForm.ThePeriod & " and Field_Code = " & NewCasesCreationForm.TheFieldCode

                RunNonQuery(sql)

                Dim TheCode As Integer
                TheCode = NewCasesCreationForm.TheFieldCode

                sql = "Insert Into Modelling_Case_Templates Values ('" & NewCasesCreationForm.TheCaseID & "', " & NewCasesCreationForm.ThePeriod & _
                      ", " & NewCasesCreationForm.TheFieldCode & ", " & NewCasesCreationForm.TheFieldOrder & ", '" & FieldBox.Text.ToString & "', '" & _
                      TypeBox.SelectedValue.ToString & "', 'Formula' , '" & TheRealSQL & "', '" & TheDisplaySQL & "', '" & TableBox.SelectedValue.ToString & "', 'No')"


                RunNonQuery(sql)
                With NewCasesCreationForm
                    .LoadFieldGrid(0)
                    .FieldGrid.Rows(.GetTheRow(TheCode, "Field_Code")).Selected = True
                End With
                Exit Sub

            End If

            If ProductButton.Checked = True Then

                CheckFields()
                If ClearFields = False Then Exit Sub

                sql = "Delete from Modelling_Case_Templates where DataCut_ID = '" & NewCasesCreationForm.TheCaseID & "' and Period_ID = " & _
                      NewCasesCreationForm.ThePeriod & " and Field_Code = " & NewCasesCreationForm.TheFieldCode

                RunNonQuery(sql)

                Dim TheCode As Integer
                TheCode = NewCasesCreationForm.TheFieldCode

                sql = "Insert Into Modelling_Case_Templates Values ('" & NewCasesCreationForm.TheCaseID & "', " & NewCasesCreationForm.ThePeriod & _
                      ", " & NewCasesCreationForm.TheFieldCode & ", " & NewCasesCreationForm.TheFieldOrder & ", '" & FieldBox.Text.ToString & "', '" & _
                      TypeBox.SelectedValue.ToString & "', 'Product' , '" & Trim(ScenarioBox.SelectedValue.ToString) & "/" & Trim(ProductBox.SelectedValue.ToString) & _
                      "', '" & Trim(ScenarioBox.Text.ToString) & " - " & Trim(ProductBox.Text.ToString) & "', '" & TableBox.SelectedValue.ToString & "', 'No')"

                RunNonQuery(sql)
                With NewCasesCreationForm
                    .LoadFieldGrid(0)
                    .FieldGrid.Rows(.GetTheRow(TheCode, "Field_Code")).Selected = True
                End With
                Exit Sub

            End If

            Exit Sub

        End If

    End Sub

    Private Function GetFieldCode() As Integer

        Dim sql As String
        Dim TheData As DataView

        sql = "Select Coalesce(Max(Field_Code),0) as [FieldCode] from Modelling_Case_Templates where DataCut_ID = '" & NewCasesCreationForm.TheCaseID & _
              "' and Period_ID = " & NewCasesCreationForm.ThePeriod

        TheData = LoadDataView(sql)

        GetFieldCode = TheData(0).Item("FieldCode") + 10

        Return GetFieldCode

    End Function

    Private Function GetOrderCode() As Integer

        Dim sql As String
        Dim TheData As DataView

        sql = "Select Coalesce(Max(Field_Order),0) as [OrderCode] from Modelling_Case_Templates where DataCut_ID = '" & NewCasesCreationForm.TheCaseID & _
              "' and Period_ID = " & NewCasesCreationForm.ThePeriod

        TheData = LoadDataView(sql)

        GetOrderCode = TheData(0).Item("OrderCode") + 10

        Return GetOrderCode

    End Function

    Private Sub SaveNewButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveNewButton.Click

        If FormulaButton.Checked Then
            If TestFormula() = False Then
                MessageBox.Show("There is an error in your formula, please review it", "Error in Formula", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End If
        End If

        Save()

        If ClearFields = True Then
            ClearForm()
        End If

    End Sub

    Private Sub ClearForm()

        FieldBox.Clear()

        LoadCombos()
        TypeBox.SelectedValue = ""
        TableBox.SelectedValue = ""
        FormulaElements.Clear()
        SQLElemets.Clear()

        FormulaPanel.Hide()
        ProductPanel.Hide()

        UserButton.Checked = True

        LoadScenarioBox()
        LoadProductBox()

        FieldBox.Focus()

    End Sub

    Private Sub SaveCloseButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveCloseButton.Click
        If FormulaButton.Checked Then
            If TestFormula() = False Then
                MessageBox.Show("There is an error in your formula, please review it", "Error in Formula", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End If
        End If

        Save()

        If ClearFields = True Then
            Me.Close()
        End If

    End Sub

    Private Sub BuildRealSQL(ByVal Text As String)

        TheRealSQL = TheRealSQL & Text

    End Sub

    Private Sub BuildDisplaySQL(ByVal Text As String)

        TheDisplaySQL = TheDisplaySQL & Text
        FormulaBox.Text = TheDisplaySQL
        FormulaElements.Add(Text)
        SQLElemets.Add(Text)

    End Sub

    Private Sub One_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles One.Click
        Calcbox.Text = Calcbox.Text & "1"
        'BuildRealSQL("1")
        'BuildDisplaySQL("1")

    End Sub

    Private Sub Two_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Two.Click
        Calcbox.Text = Calcbox.Text & "2"
        'BuildRealSQL("2")
        'BuildDisplaySQL("2")

    End Sub

    Private Sub Three_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Three.Click
        Calcbox.Text = Calcbox.Text & "3"
        'BuildRealSQL("3")
        'BuildDisplaySQL("3")

    End Sub

    Private Sub Four_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Four.Click
        Calcbox.Text = Calcbox.Text & "4"
        'BuildRealSQL("4")
        'BuildDisplaySQL("4")

    End Sub

    Private Sub Five_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Five.Click
        Calcbox.Text = Calcbox.Text & "5"
        'BuildRealSQL("5")
        'BuildDisplaySQL("5")

    End Sub

    Private Sub Six_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Six.Click
        Calcbox.Text = Calcbox.Text & "6"
        'BuildRealSQL("6")
        'BuildDisplaySQL("6")

    End Sub

    Private Sub Seven_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Seven.Click
        Calcbox.Text = Calcbox.Text & "7"
        'BuildRealSQL("7")
        'BuildDisplaySQL("7")

    End Sub

    Private Sub Eight_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Eight.Click
        Calcbox.Text = Calcbox.Text & "8"
        'BuildRealSQL("8")
        'BuildDisplaySQL("8")

    End Sub

    Private Sub Nine_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Nine.Click
        Calcbox.Text = Calcbox.Text & "9"
        'BuildRealSQL("9")
        'BuildDisplaySQL("9")

    End Sub

    Private Sub Zero_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Zero.Click
        Calcbox.Text = Calcbox.Text & "0"
        'BuildRealSQL("0")
        'BuildDisplaySQL("0")

    End Sub

    Private Sub PlusButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PlusButton.Click
        If CanAddElement("+") Then
            BuildRealSQL(" + ")
            BuildDisplaySQL(" + ")
        End If
        

    End Sub

    Private Sub SubtractButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SubtractButton.Click
        If CanAddElement("-") Then
            BuildRealSQL(" - ")
            BuildDisplaySQL(" - ")
        End If

    End Sub

    Private Sub DivideButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DivideButton.Click
        If CanAddElement("/") Then
            BuildRealSQL(" / ")
            BuildDisplaySQL(" / ")
        End If
    End Sub

    Private Sub MutliplyButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MutliplyButton.Click
        If CanAddElement("*") Then
            BuildRealSQL(" * ")
            BuildDisplaySQL(" * ")
        End If
    End Sub

    Private Sub OpenBracketButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OpenBracketButton.Click
        If CanAddElement("(") Then
            BuildRealSQL("(")
            BuildDisplaySQL("(")
        End If
    End Sub

    Private Sub CloseBracketButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CloseBracketButton.Click
        If CanAddElement(")") Then
            BuildRealSQL(")")
            BuildDisplaySQL(")")
        End If
    End Sub

    Private Sub TableBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TableBox.SelectedIndexChanged

        If TableBox.Text = "Other" Then
            TypeBox.Enabled = True
            Exit Sub
        End If

        If TableBox.Text = "Learners" Then
            TypeBox.SelectedValue = "INT"
            TypeBox.Enabled = False
            Exit Sub
        End If

        If TableBox.Text = "Factors" Then
            TypeBox.SelectedValue = "DEC"
            TypeBox.Enabled = False
            Exit Sub
        End If

        If TableBox.Text = "SLNs" Then
            TypeBox.SelectedValue = "DEC"
            TypeBox.Enabled = False
            Exit Sub
        End If

        If TableBox.Text = "Rates" Or TableBox.Text = "Funding" Then
            TypeBox.SelectedValue = "MONEY"
            TypeBox.Enabled = False
            Exit Sub
        End If

    End Sub

    Private Sub ScenarioBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ScenarioBox.SelectedIndexChanged

        If FireEvents = False Then Exit Sub
        If ScenarioBox.SelectedValue Is Nothing Then Exit Sub

        TheScenario = Trim(ScenarioBox.SelectedValue.ToString)

        LoadProductBox()

    End Sub


    Private Sub CloseButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CloseButton.Click

        Me.Close()

    End Sub

    Private Sub PreviousButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PreviousButton.Click

        CheckFields()
        If ClearFields = False Then Exit Sub

        Save()
        TheCurrentRow = NewCasesCreationForm.GetCurrentRow() - 1

        With NewCasesCreationForm.FieldGrid

            If TheCurrentRow = -1 Then Exit Sub
            .Rows(TheCurrentRow).Selected = True
            NewCasesCreationForm.UpdateVarValues()
        End With

        LoadMe()

    End Sub

    Private Sub NextButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextButton.Click

        CheckFields()
        If ClearFields = False Then Exit Sub

        Save()
        TheCurrentRow = NewCasesCreationForm.GetCurrentRow() + 1

        With NewCasesCreationForm.FieldGrid

            If TheCurrentRow = .Rows.Count Then Exit Sub
            .Rows(TheCurrentRow).Selected = True
            NewCasesCreationForm.UpdateVarValues()
        End With

        LoadMe()

    End Sub

    Private Sub CheckFields()

        If FormulaButton.Checked = True Then
            If FieldBox.Text.Length = 0 Or TypeBox.Text.Length = 0 Or TableBox.Text.Length = 0 Or FormulaBox.Text.Length = 0 Then
                MessageBox.Show("You have not completed all of the information required. Please complete all fields and try again", "Error", MessageBoxButtons.OK)
                ClearFields = False
                Exit Sub
            End If
        Else
            If FieldBox.Text.Length = 0 Or TypeBox.Text.Length = 0 Or TableBox.Text.Length = 0 Then
                MessageBox.Show("You have not completed all of the information required. Please complete all fields and try again", "Error", MessageBoxButtons.OK)
                ClearFields = False
                Exit Sub
            End If
        End If

        ClearFields = True

    End Sub

    Private Sub FillForm()

        Dim sql As String
        Dim TheData As DataView

        sql = "Select * from Modelling_Case_Templates where DataCut_ID = '" & NewCasesCreationForm.TheCaseID & _
              "' and Period_ID = " & NewCasesCreationForm.ThePeriod & " and Field_Code = " & NewCasesCreationForm.TheFieldCode

        TheData = LoadDataView(sql)

        If TheData.Count <> 1 Then Exit Sub

        FieldBox.Text = TheData(0).Item("Field_Name").ToString
        TableBox.SelectedValue = TheData(0).Item("Modelling_Table").ToString
        TypeBox.SelectedValue = TheData(0).Item("Type").ToString

        Select Case TheData(0).Item("Formula").ToString

            'This is a user field then
            Case "User Input"
                UserButtonClick()

            Case "Approved Total"
                TotalButtonClick()

                'This is a formula field
            Case "Formula"
                TheRealSQL = TheData(0).Item("SQL").ToString
                TheDisplaySQL = TheData(0).Item("DisplaySQL").ToString

                FormulaElements.Clear()
                FormulaElements.Add(TheDisplaySQL)

                SQLElemets.Clear()
                SQLElemets.Add(TheRealSQL)

                FormulaButtonClick()

                'And a product/datacut
            Case "Product"
                TheRealSQL = ""
                TheDisplaySQL = ""

                Dim TestString As String
                Dim Slash As Integer
                TestString = Trim(TheData(0).Item("SQL").ToString)
                'Find position of the Slash
                Slash = InStr(TestString, "/")
                TheScenario = Trim(Mid(TestString, 1, Slash - 1))
                TheProduct = Trim(Mid(TestString, Slash + 1, Len(TestString)))

                'TheScenario = TheData(0).Item("SQL").ToString
                'TheProduct = TheData(0).Item("DisplaySQL").ToString
                ProductButtonClick()

        End Select

    End Sub

    Private Sub UserButtonClick()

        'Sort the buttons
        UserButton.Checked = True
        FormulaButton.Checked = False
        ProductButton.Checked = False
        ApprovedTotalButton.Checked = False

        'Sort the panels
        FormulaPanel.Hide()
        ProductPanel.Hide()

    End Sub

    Private Sub FormulaButtonClick()

        'Sort the buttons
        UserButton.Checked = False
        FormulaButton.Checked = True
        ProductButton.Checked = False
        ApprovedTotalButton.Checked = False

        'Sort the panels
        FormulaPanel.Show()
        ProductPanel.Hide()
        FormulaBox.ReadOnly = True

        'Decide what to do with the fieldlist and the formulabox
        If WhoCalled = "EditField" Then
            LoadFieldListBox()
            FormulaBox.Text = TheDisplaySQL
        Else
            LoadFieldListBox()
            ClearFormulaBox()
        End If

    End Sub

    Private Sub ProductButtonClick()

        'Sort the buttons
        UserButton.Checked = False
        FormulaButton.Checked = False
        ProductButton.Checked = True
        ApprovedTotalButton.Checked = False


        'Sort the panels
        FormulaPanel.Hide()
        ProductPanel.Show()

        'Decide what to do with the combo boxes

        If WhoCalled = "EditField" Then
            ScenarioBox.SelectedValue = TheScenario
            ProductBox.SelectedValue = TheProduct
        Else
            LoadScenarioBox()
            LoadProductBox()
        End If


    End Sub

    Private Sub TotalButtonClick()

        'Sort the buttons
        UserButton.Checked = False
        FormulaButton.Checked = False
        ProductButton.Checked = False
        ApprovedTotalButton.Checked = True

        'Sort the panels
        FormulaPanel.Hide()
        ProductPanel.Hide()


    End Sub


    Private Sub UserButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UserButton.Click

        UserButtonClick()

    End Sub

    Private Sub FormulaButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FormulaButton.Click

        FormulaButtonClick()

    End Sub

    Private Sub ProductButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProductButton.Click

        ProductButtonClick()

    End Sub

    Private Sub LoadFieldListBox()

        Dim sql As String
        Dim TheData As DataView

        ListBoxCalling = True

        sql = "Select * from Modelling_Case_Templates where DataCut_ID = '" & NewCasesCreationForm.TheCaseID & _
               "' and Period_ID = " & NewCasesCreationForm.ThePeriod & " Order by Field_Order"
        TheData = LoadDataView(sql)

        FieldListBox.DataSource = TheData
        FieldListBox.DisplayMember = "Field_Name"
        FieldListBox.ValueMember = "Field_Code"
        FieldListBox.SelectionMode = SelectionMode.One

        ListBoxCalling = False

    End Sub

    Private Sub FieldListBox_SelectedValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FieldListBox.SelectedValueChanged

        If FireEvents = False Then Exit Sub
        If ListBoxCalling = True Then Exit Sub

        If FieldListBox.SelectedValue Is Nothing Then Exit Sub

        If CanAddElement(FieldListBox.Text) Then
            TheDisplaySQL = TheDisplaySQL & FieldListBox.Text
            TheRealSQL = TheRealSQL & "[" & FieldListBox.SelectedValue.ToString & "]"
            FormulaElements.Add(FieldListBox.Text)
            SQLElemets.Add(FieldListBox.text)
            FormulaBox.Text = TheDisplaySQL
        End If

    End Sub

    Private Sub ClearFormulaBox_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClearBox.Click

        ClearFormulaBox()

    End Sub

    Sub LoadScenarioBox()

        If FireEvents = False Then Exit Sub
        ScenarioBoxReady = False

        LoadComboBox(ScenarioBox, "Select * From Modelling_Scenarios Where User_Name = '" & CurrentUser.Name & "' Order by Scenario_Description ", "Scenario_ID", "Scenario_Description")
        ScenarioBox.DropDownStyle = ComboBoxStyle.DropDownList

        TheScenario = Trim(ScenarioBox.SelectedValue.ToString)
        ScenarioBoxReady = True

    End Sub

    Sub LoadProductBox()

        If FireEvents = False Then Exit Sub
        If ScenarioBoxReady = False Then Exit Sub

        'If ProductBox.SelectedValue Is Nothing Then Exit Sub

        LoadComboBox(ProductBox, "Select * From Modelling_Products where Scenario_ID = '" & TheScenario & "' Order by Product_Description", "Product_Number", "Product_Description")
        ProductBox.DropDownStyle = ComboBoxStyle.DropDownList
        TheProduct = Trim(ProductBox.SelectedValue.ToString)

    End Sub

    Private Sub ClearFormulaBox()

        FormulaBox.Clear()
        Calcbox.Clear()
        TheRealSQL = ""
        TheDisplaySQL = ""
        FormulaElements.Clear()
        SQLElemets.Clear()

    End Sub

    Private Sub CheckingItOut()

        Dim Value As Double = 0
        Dim TheFormula As String = ""
        'This contains my fields replaced with 1

        TheFormula = Trim(FormulaBox.Text)

        Dim BracketPos As Integer
        BracketPos = InStr(TheFormula, "[")

        If TheFormula.Contains("/0") Then
            Value = Nothing
        Else
            Value = New DataTable().Compute(TheFormula, "")
        End If

    End Sub

    Private Sub AddToBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddToBut.Click
        Dim Itest As Integer

        Try
            Itest = Calcbox.Text
        Catch ex As Exception
            Calcbox.Focus()
            MessageBox.Show("This is not a valid number, please enter a number", "Not a valid number", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

        If CanAddElement(Calcbox.Text) Then

            Dim TheElement As String = ""

            TheElement = " cast(" & Calcbox.Text & " as decimal(18,8)) "

            BuildRealSQL(TheElement)
            BuildDisplaySQL(Calcbox.Text)
            Calcbox.Text = ""
        End If

    End Sub

    Function BuildSQLfromList(ByVal RealSQL As Boolean) As String

        Dim i As Integer
        Dim TheString As String = ""

        If FormulaElements.Count = 0 Then
            Return ""
            Exit Function
        End If

        If RealSQL = False Then
            
            For i = 0 To FormulaElements.Count - 1
                TheString = TheString & FormulaElements(i)
            Next

        Else
            
            For i = 0 To SQLElemets.Count - 1
                TheString = TheString & SQLElemets(i)
            Next


        End If

        Return TheString

    End Function

    Private Sub UndoButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UndoButton.Click
        If FormulaElements.Count <> 0 Then
            FormulaElements.RemoveAt(FormulaElements.Count - 1)
            SQLElemets.RemoveAt(SQLElemets.Count - 1)

            TheRealSQL = BuildSQLfromList(True)
            TheDisplaySQL = BuildSQLfromList(False)
            FormulaBox.Text = TheDisplaySQL

        End If
    End Sub

    Private Function TestFormula() As Boolean

        'first replace each element with 1 (except if it is a number or a sign)

        Dim TheF As String = ""
        Dim R As Boolean
        Dim i As Integer
        Dim Testi As Integer
        Dim isint As Boolean = True
        Dim Value As Double

        If FormulaElements.Count = 0 Then
            R = False

        Else
            For i = 0 To FormulaElements.Count - 1
                Select Case Trim(FormulaElements(i))
                    Case "+", "-", "/", "*", "(", ")"
                        TheF = TheF & FormulaElements(i)
                    Case Else
                        'see if it is a number
                        isint = True

                        Try
                            Testi = Trim(FormulaElements(i))
                        Catch ex As Exception
                            isint = False
                        End Try

                        If Not isint Then
                            TheF = TheF & "1"
                        Else
                            TheF = TheF & FormulaElements(i)
                        End If
                End Select
            Next
            'now lets test TheF
            R = True
            Try
                Value = New DataTable().Compute(TheF, "")
            Catch ex As Exception
                R = False
            End Try
        End If

        Return R
    End Function

    Private Sub ApprovedTotalButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ApprovedTotalButton.Click

    End Sub
End Class